﻿//********************************************************************************//
//创建日期：<创建日期，2016-4-6>
//创建作者：<孙雪婷，sunxueting@shanghai3h.com>
//修改 2017-10-24 sunxueting
//功能说明：到账导出
//********************************************************************************//

using System;
using System.Data;
using System.IO;
using BLL;
using BLL.Access;
using DevExpress.Web;
using Entity;
using NPOI.HSSF.UserModel;
using Newtonsoft.Json;
using Web.Class;

namespace Web.Pages.YongShui._5.ShouFeiGL
{
    public partial class DaoZhangDC : BasePage
    {
        #region 属性

        /// <summary>
        ///     保存结果集
        /// </summary>
        private DataTable dtXiangXiXX
        {
            get { return ViewState["dtXiangXiXX"] as DataTable; }
            set { ViewState["dtXiangXiXX"] = value; }
        }

        #endregion

        #region 变量

        /// <summary>
        /// 数据访问操作类
        /// </summary>
        BLL.SqlHelper _splHelper = new BLL.SqlHelper();

        #endregion

        protected void btnExport_Click(object sender, EventArgs e)
        {
            if (dtXiangXiXX != null)
            {
                var exportData = new SqlHelper().GetDaoZhang().Tables[0];
                var fileName = "加价水费数据" + DateTime.Now.ToString("yyyyMMdd") + "(" + exportData.Rows.Count + "笔" +
                               DateTime.Now.ToString("yyyyMMdd") + "邮).xls";
                ExportExcel(exportData,fileName);
            }
            else
            {
                PageTools.AlertMsg(this, "请先查询数据后导出");
            }
        }

        private void ExportExcel(DataTable dt, string fileName)
        {
            var wb = new HSSFWorkbook();
            var sheet = (HSSFSheet) wb.CreateSheet("Sheet1"); //创建工作表
            //sheet.CreateFreezePane(0, 1); //冻结列头行
            var rowTitle = (HSSFRow) sheet.CreateRow(0); //创建列头行
            var columns = new[]
            {
                "业务管理部门", "电话", "业务员", "财务部门地址", "财务部门邮编", "用户名", "开户户名", "用户号", "计划量", "实用量", "加价量", "加价金额", "水量年",
                "水量月", "费用编号", "备注"
            };
            for (var i = 0; i < columns.Length; i++)
            {
                var cellTitle = (HSSFCell) rowTitle.CreateCell(i);
                cellTitle.SetCellValue(columns[i]);
            }

            var index = 1;
            foreach (DataRow row in dt.Rows)
            {
                var rowData = (HSSFRow) sheet.CreateRow(index);
                for (var i = 0; i < row.ItemArray.Length - 1; i++)
                {
                    if (i != row.ItemArray.Length - 2)
                    {
                        var cellData = (HSSFCell)rowData.CreateCell(i);
                        cellData.SetCellValue(row[i].ToString());
                    }
                    else
                    {
                        var cellData = (HSSFCell)rowData.CreateCell(i);
                        if (row[i] != DBNull.Value)
                        {
                            cellData.SetCellValue("多收"+row[i+1]);
                        }
                        continue;
                    }
                }

                index++;
            }

            var stream = new MemoryStream();
            wb.Write(stream);
            var buf = stream.ToArray();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
            Response.BinaryWrite(buf);
            Response.End();
        }

        #region 变量

        /// <summary>
        ///     用户信息对象
        /// </summary>
        private YS_YG_YONGHUXX _YS_YG_YONGHUXX = new YS_YG_YONGHUXX();

        /// <summary>
        ///     用户信息BLL
        /// </summary>
        private YS_YG_YONGHUXXBLL _YS_YG_YONGHUXXBLL = new YS_YG_YONGHUXXBLL();

        /// <summary>
        ///     管理块信息
        /// </summary>
        private YS_JX_GUANLIK _YS_JX_GUANLIK = new YS_JX_GUANLIK();

        /// <summary>
        ///     管理块信息BLL
        /// </summary>
        private readonly YS_JX_GUANLIKBLL _YS_JX_GUANLIKBLL = new YS_JX_GUANLIKBLL();

        /// <summary>
        ///     存储过程调用类
        /// </summary>
        private SpHelper _SpHelper = new SpHelper();

        #endregion

        #region 事件

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) LoadData();
            //用于执行导出
            //this.ScriptManager1.RegisterPostBackControl(this.btnExportExcel);
            gvXiangXiXX.DataSource = dtXiangXiXX;
            gvXiangXiXX.DataBind();
        }

        /// <summary>
        ///     查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnChaXun_Click(object sender, EventArgs e)
        {
            GetData();
        }

        /// <summary>
        ///     根据用户号定位
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDingWei_Click(object sender, EventArgs e)
        {
            if (txtYongHuH.Text.Trim() != "")
            {
                var id = PageTools.FocusOpreateRecord(dtXiangXiXX, gvXiangXiXX, "S_YONGHUH", txtYongHuH.Text, true);
                if (id == -1) PageTools.AlertMsg(this, "当前数据中没有你要找的数据,请检查你输入的条件是否正确。");
            }
        }

        /// <summary>
        ///     导出excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        /// <summary>
        ///     初始数据加载
        /// </summary>
        private void LoadData()
        {
            gvXiangXiXX.SettingsPager.Mode = GridViewPagerMode.EndlessPaging;
            LoadXingZhengQ();
            //默认显示账务年月
            var water_y_m = BusinessTool.YS_GETSETTINGSVALUE(eSETTINGS_KEYNAME.ZW_Y_M.ToString());
            txtShuiLiangNY.Text = water_y_m;
        }

        /// <summary>
        ///     加载区信息
        /// </summary>
        public void LoadXingZhengQ()
        {
            var QuXX = _YS_JX_GUANLIKBLL.SelectYS_JX_GUANLIKsDynamic(
                "(s_zhandian Between '51' and '59') and i_leixing in (0,1) and i_jlzt=0 order by s_guanlikh", null);
            for (var i = 0; i < QuXX.Rows.Count; i++)
            {
                var s_guanlikh = QuXX.Rows[i]["s_guanlikh"].ToString();
                var s_guanlikmc = QuXX.Rows[i]["s_guanlikmc"].ToString();
                cmbGuanLiK.Items.Add(s_guanlikmc);
            }

            //cmbGuanLiK.SelectedIndex = 0;
        }

        /// <summary>
        ///     根据页面参数查询相对应的数据
        /// </summary>
        public void GetData()
        {
            var v_pcid = 232;
            //管理块
            var glk = cmbGuanLiK.Text;
            var guanlik = "00";
            if (glk != "全部")
            {
                var dt_glk =
                    _YS_JX_GUANLIKBLL.SelectYS_JX_GUANLIKsDynamic(string.Format("s_guanlikmc='{0}' and i_jlzt=0", glk),
                        null);
                guanlik = dt_glk.Rows[0]["s_guanlikh"].ToString();
            }

            //账务年月(0)/水量年月(1)
            var v_type = Convert.ToInt32(cmbNianYue.Value);
            //水量年
            var v_y = Convert.ToInt32(txtShuiLiangNY.Text) / 100;
            //水量月
            var v_m = Convert.ToInt32(txtShuiLiangNY.Text) % 100;
            ////用户号
            //string v_yonghuh = "";
            //int count1 = 0;
            //收费类型
            var shoufei = Convert.ToInt32(cmbLeiXing.Value);
            //打印类型
            var print = Convert.ToInt32(cmbShiFouDY.Value);


            dtXiangXiXX = SearchSpHelper.P_YSSF_SHUIFEIZD(guanlik, v_type, v_y, v_m, shoufei, print).Tables[0];

            decimal sumMoney = 0;
            for (var i = 0; i < dtXiangXiXX.Rows.Count; i++)
                sumMoney += Convert.ToDecimal(dtXiangXiXX.Rows[i]["N_JJJE"]);
            zjl.Text = "共" + dtXiangXiXX.Rows.Count + "条,加价金额合计:" + sumMoney.ToString("0.00");
            zjl.Font.Bold = true;
            gvXiangXiXX.DataSource = dtXiangXiXX;
            gvXiangXiXX.DataBind();
            //if (true)
            //{

            //}
        }

        protected void ASPxCallback1_Callback(object source, CallbackEventArgs e)
        {
            string sMsg = "";
            bool bResult = false;
            try
            {
                if ("showSetPrintBtn" == e.Parameter)
                {
                    bResult = Convert.ToInt32(cmbShiFouDY.Value) == 0;
                }
            }
            catch (Exception ex)
            {
                sMsg = ex.Message;
            }
            e.Result = JsonConvert.SerializeObject(new
            {
                succ = bResult,
                msg = sMsg
            });
        }

        protected void btnSetPrinted_Click(object sender, EventArgs e)
        {
            int o_status = 0;
            string o_msg = "";
            _splHelper.p_yssf_shuifeizd_update(UserID, out o_status, out o_msg);
            if (0 == o_status) GetData();
            else PageTools.AlertMsg(this, o_msg);
        }

        #endregion
    }
}